package com.py.credit.service.impl;

import com.py.credit.dao.OfficeDao;
import com.py.credit.dao.StatisticsDao;
import com.py.credit.entity.Statistics;
import com.py.credit.entity.StatisticsCount;
import com.py.credit.service.StatisticsService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Service
public class StatisticsServiceImpl implements StatisticsService {

    @Autowired
    private StatisticsDao statisticsDao;

    @Autowired
    private OfficeDao officeDao;

    @Override
    public List<Statistics> selectCjInfo(String type, String startTime, String endTime) {
        if (type.equals("C")) {
            return statisticsDao.selectFrCjInfo(type, startTime, endTime);
        } else {
            return statisticsDao.selectZrrCjInfo(type, startTime, endTime);
        }
    }

    @Override
    public List<Statistics> selectJlInfo(String type, String startTime, String endTime) {
        if (type.equals("C")) {
            return statisticsDao.selectFrJlInfo(type, startTime, endTime);
        } else {
            return statisticsDao.selectZrrJlInfo(type, startTime, endTime);
        }
    }

    @Override
    public List<Statistics> selectSixMorthCjInfo(String type, String startTime, String endTime) {
        if (type.equals("C")) {
            return statisticsDao.selectFrSixMorthCjInfo(type, startTime, endTime);
        } else {
            return statisticsDao.selectZrrSixMorthCjInfo(type, startTime, endTime);
        }
    }

    @Override
    public List<Statistics> selectSixMorthJlInfo(String type, String startTime, String endTime) {
        if (type.equals("C")) {
            return statisticsDao.selectFrSixMorthJlInfo(type, startTime, endTime);
        } else {
            return statisticsDao.selectZrrSixMorthJlInfo(type, startTime, endTime);
        }
    }

    @Override
    public List<Statistics> selectDeptYgInfo(String type, String startTime, String endTime) {
        if (type.equals("C")) {
            return this.selectFrDeptYgInfo(type, startTime, endTime);
        } else {
            return this.selectZrrDeptYgInfo(type, startTime, endTime);
        }
    }

    @Override
    public List<Statistics> selectDeptSjInfo(String type, String startTime, String endTime,List<Statistics> deptYg) {
        List<Statistics> statisticsList=new ArrayList<Statistics>();

        if (type.equals("C")) {
            for (Statistics statistics:deptYg
                    ) {
                Statistics frStatistics=new Statistics();
                frStatistics.setDate(statistics.getDate());
                frStatistics.setNum(statisticsDao.selectFrDeptSjInfo(startTime, endTime,officeDao.selectTopAndDownwardId(statistics.getId())));
                statisticsList.add(frStatistics);
            }
        } else {
            for (Statistics statistics:deptYg
                    ) {
                Statistics frStatistics=new Statistics();
                frStatistics.setDate(statistics.getDate());
                frStatistics.setNum(statisticsDao.selectZrrDeptSjInfo(startTime, endTime,officeDao.selectTopAndDownwardId(statistics.getId())));
                statisticsList.add(frStatistics);
            }
        }

        return statisticsList;
    }

    @Override
    public int selectFrCjZs(String type, String startTime, String endTime) {
        return statisticsDao.selectFrCjZs(type, startTime, endTime);
    }

    @Override
    public List<Statistics> selectFrCjYC(String type, String startTime, String endTime) {
        return statisticsDao.selectFrCjYC(type, startTime, endTime);
    }

    @Override
    public int selectZrrCjZs(String type, String startTime, String endTime) {
        return statisticsDao.selectZrrCjZs(type, startTime, endTime);
    }

    @Override
    public List<Statistics> selectZrrCjYC(String type, String startTime, String endTime) {
        return statisticsDao.selectZrrCjYC(type, startTime, endTime);
    }

    @Override
    public void excelExport(HttpServletResponse response, String startTime, String endTime) throws IOException {
        queryTableExecle(response, startTime, endTime);
    }

    @Override
    public List<StatisticsCount> selectJcgzDistribution(String type, String jclb, String startTime, String endTime) {
        return statisticsDao.selectJcgzDistribution(type, jclb, startTime, endTime);
    }

    @Override
    public List<StatisticsCount> selectJcCount(String type, String jclb, String startTime, String endTime) {
        return statisticsDao.selectJcCount(type, jclb, startTime, endTime);
    }

    private void queryTableExecle(HttpServletResponse response, String startTime, String endTime) throws IOException {
        // 生成提示信息
        response.setContentType("application/vnd.ms-excel");
        HSSFWorkbook workbook = null;
        OutputStream fOut = null;
        String codedFileName = "联合奖惩统计分析";
        //进行转码，使其支持中文文件名
//				codedFileName = URLEncoder.encode(codedFileName, "UTF-8");
//				String agent = request.getHeader("USER-AGENT");
//				if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
//					codedFileName = "=?UTF-8?B?" + (new String(Base64.encodeBase64(codedFileName.getBytes("UTF-8")))) + "?=";
//				} else {
//					codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF-8");
//				}
        codedFileName = URLEncoder.encode(codedFileName, "UTF-8");
        response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
        // 产生工作簿对象
        workbook = new HSSFWorkbook();
        // 产生工作表对象

        String[] strBT1 = {"时间", "法人激励发起数"};
        String[] strBT2 = {"时间", "法人惩戒发起数"};
        String[] strBT3 = {"时间", "自然人激励发起数"};
        String[] strBT4 = {"时间", "自然人惩戒发起数"};

        //法人惩戒和激励信息
        List<Statistics> frcjTotal = statisticsDao.selectFrCjInfo("C", startTime, endTime);
        List<Statistics> frjlTotal = statisticsDao.selectFrJlInfo("C", startTime, endTime);
        //自然人惩戒和激励信息
        List<Statistics> zrrcjTotal = statisticsDao.selectZrrCjInfo("P", startTime, endTime);
        List<Statistics> zrrjlTotal = statisticsDao.selectZrrJlInfo("P", startTime, endTime);

        HSSFSheet sheet = workbook.createSheet();
        String sheet1Name = "法人联合激励发起数";
        workbook.setSheetName(0, sheet1Name);
        HSSFRow row = null;
        HSSFCell cell = null;
        // 创建标题行
        row = sheet.createRow(0);
        for (int i = 0; i < strBT1.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT1[i]);
        }
        for (int i = 0; i < frjlTotal.size(); i++) {
            row = sheet.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frjlTotal.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frjlTotal.get(i).getNum());
        }

        HSSFSheet sheet2 = workbook.createSheet();
        String sheet2Name = "法人联合奖惩发起数";
        workbook.setSheetName(1, sheet2Name);
        row = null;
        cell = null;
        // 创建标题行
        row = sheet2.createRow(0);
        for (int i = 0; i < strBT2.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT2[i]);
        }
        for (int i = 0; i < frcjTotal.size(); i++) {
            row = sheet2.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frcjTotal.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frcjTotal.get(i).getNum());
        }

        HSSFSheet sheet3 = workbook.createSheet();
        String sheet3Name = "自然人联合激励发起数";
        workbook.setSheetName(2, sheet3Name);
        row = null;
        cell = null;
        // 创建标题行
        row = sheet3.createRow(0);
        for (int i = 0; i < strBT3.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT3[i]);
        }
        for (int i = 0; i < zrrjlTotal.size(); i++) {
            row = sheet3.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrjlTotal.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrjlTotal.get(i).getNum());
        }

        HSSFSheet sheet4 = workbook.createSheet();
        String sheet4Name = "自然人联合惩戒发起数";
        workbook.setSheetName(3, sheet4Name);
        row = null;
        cell = null;
        // 创建标题行
        row = sheet4.createRow(0);
        for (int i = 0; i < strBT4.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT4[i]);
        }
        for (int i = 0; i < zrrcjTotal.size(); i++) {
            row = sheet4.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrcjTotal.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrcjTotal.get(i).getNum());
        }

        Date dNow = new Date();   //当前时间
        Date dBefore = new Date();
        Calendar calendar = Calendar.getInstance(); //得到日历
        calendar.setTime(dNow);//把当前时间赋给日历
        calendar.add(calendar.MONTH, -5);  //设置为前6月
        dBefore = calendar.getTime();   //得到前6月的时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM"); //设置时间格式
        String defaultStartDate = sdf.format(dBefore);    //格式化前6月的时间
        String defaultEndDate = sdf.format(dNow); //格式化当前时间
        List<String> months = new ArrayList<String>();

        Calendar min = Calendar.getInstance();
        Calendar max = Calendar.getInstance();

        min.setTime(dBefore);
        min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);

        max.setTime(dNow);
        max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);

        Calendar curr = min;
        while (curr.before(max)) {
            months.add(sdf.format(curr.getTime()));
            curr.add(Calendar.MONTH, 1);
        }
        //法人惩戒和激励信息
        List<Statistics> frcj6Total = statisticsDao.selectFrSixMorthCjInfo("C", defaultStartDate, defaultEndDate);
        List<Statistics> frjl6Total = statisticsDao.selectFrSixMorthJlInfo("C", defaultStartDate, defaultEndDate);
        //自然人惩戒和激励信息
        List<Statistics> zrrcj6Total = statisticsDao.selectZrrSixMorthCjInfo("P", defaultStartDate, defaultEndDate);
        List<Statistics> zrrjl6Total = statisticsDao.selectZrrSixMorthJlInfo("P", defaultStartDate, defaultEndDate);
        String[] m6 = {"时间", "法人激励发起数", "法人惩戒发起数", "自然人激励发起数", "自然人惩戒发起数"};

        HSSFSheet sheet5 = workbook.createSheet();
        String sheet5Name = "近6个月联合奖惩发起数量趋势";
        workbook.setSheetName(4, sheet5Name);
        row = null;
        cell = null;
        // 创建标题行
        row = sheet5.createRow(0);
        for (int i = 0; i < m6.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m6[i]);
        }
        for (int i = 0; i < months.size(); i++) {
            row = sheet5.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(months.get(i));

            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            for (int a = 0; a < frjl6Total.size(); a++) {
                if (frjl6Total.get(a).getDate().equals(months.get(i))) {
                    cell.setCellValue(frjl6Total.get(a).getNum());
                    break;
                } else {
                    cell.setCellValue("");
                }
            }
            cell = row.createCell(2);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            for (int a = 0; a < frcj6Total.size(); a++) {
                if (frcj6Total.get(a).getDate().equals(months.get(i))) {
                    cell.setCellValue(frcj6Total.get(a).getNum());
                    break;
                } else {
                    cell.setCellValue("");
                }
            }
            cell = row.createCell(3);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            for (int a = 0; a < zrrjl6Total.size(); a++) {
                if (zrrjl6Total.get(a).getDate().equals(months.get(i))) {
                    cell.setCellValue(zrrjl6Total.get(a).getNum());
                    break;
                } else {
                    cell.setCellValue("");
                }
            }

            cell = row.createCell(4);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            for (int a = 0; a < zrrcj6Total.size(); a++) {
                if (zrrcj6Total.get(a).getDate().equals(months.get(i))) {
                    cell.setCellValue(zrrcj6Total.get(a).getNum());
                    break;
                } else {
                    cell.setCellValue("");
                }
            }
        }

        //法人惩戒和激励信息
        List<Statistics> frygTop10 = this.selectDeptYgInfo("C", startTime, endTime);
        List<Statistics> frsjTop10 = this.selectDeptSjInfo("C", startTime, endTime,frygTop10);
        //自然人惩戒和激励信息
        List<Statistics> zrrygTop10 = this.selectDeptYgInfo("P", startTime, endTime);
        List<Statistics> zrrsjTop10 = this.selectDeptSjInfo("P", startTime, endTime,zrrygTop10);

        HSSFSheet sheet6 = workbook.createSheet();
        String sheet6Name = "各部门联合奖惩反馈情况top10排名(法人)";
        workbook.setSheetName(5, sheet6Name);
        row = null;
        cell = null;
        String[] m10 = {"单位名称", "应该响应数", "实际响应数"};
        row = sheet6.createRow(0);
        for (int i = 0; i < m10.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m10[i]);
        }
        for (int i = 0; i < frygTop10.size(); i++) {
            row = sheet6.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frygTop10.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frygTop10.get(i).getNum());
            cell = row.createCell(2);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (frsjTop10.size() <= i) {
                cell.setCellValue("0");
                continue;
            }
            if (null == frsjTop10.get(i)) {
                cell.setCellValue("0");
            } else {
                cell.setCellValue(frsjTop10.get(i).getNum());
            }
        }

        HSSFSheet sheet7 = workbook.createSheet();
        String sheet7Name = "各部门联合奖惩反馈情况top10排名(自然人)";
        workbook.setSheetName(6, sheet7Name);
        row = null;
        cell = null;
        row = sheet7.createRow(0);
        for (int i = 0; i < m10.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m10[i]);
        }
        for (int i = 0; i < zrrygTop10.size(); i++) {
            row = sheet7.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrygTop10.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrygTop10.get(i).getNum());
            cell = row.createCell(2);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (zrrsjTop10.size() <= i) {
                cell.setCellValue("0");
                continue;
            }
            if (null == zrrsjTop10.get(i)) {
                cell.setCellValue("0");
            } else {
                cell.setCellValue(zrrsjTop10.get(i).getNum());
            }
        }
        //法人惩戒和激励信息
        int frpushTotal = statisticsDao.selectFrCjZs("C", startTime, endTime);
        List<Statistics> frpush = statisticsDao.selectFrCjYC("C", startTime, endTime);
        //自然人惩戒和激励信息
        int zrrpushTotal = statisticsDao.selectZrrCjZs("P", startTime, endTime);
        List<Statistics> zrrpush = statisticsDao.selectZrrCjYC("P", startTime, endTime);

        HSSFSheet sheet8 = workbook.createSheet();
        String sheet8Name = "联合奖惩推动效果图(法人)";
        workbook.setSheetName(7, sheet8Name);
        row = null;
        cell = null;
        String[] push = {"单位名称", "移出数", "总数"};
        row = sheet8.createRow(0);
        for (int i = 0; i < push.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(push[i]);
        }
        for (int i = 0; i < frpush.size(); i++) {
            row = sheet8.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frpush.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(frpush.get(i).getNum());
            if (i == 0) {
                cell = row.createCell(2);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(frpushTotal);
            }
        }

        HSSFSheet sheet9 = workbook.createSheet();
        String sheet9Name = "联合奖惩推动效果图(自然人)";
        workbook.setSheetName(8, sheet9Name);
        row = null;
        cell = null;
        row = sheet9.createRow(0);
        for (int i = 0; i < push.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(push[i]);
        }
        for (int i = 0; i < zrrpush.size(); i++) {
            row = sheet9.createRow((int) i + 1);
            cell = row.createCell(0);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrpush.get(i).getDate());
            cell = row.createCell(1);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(zrrpush.get(i).getNum());
            if (i == 0) {
                cell = row.createCell(2);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(zrrpushTotal);
            }
        }

        fOut = response.getOutputStream();
        workbook.write(fOut);
        workbook.close();
        fOut.flush();
        fOut.close();
    }

    private List<Statistics> selectFrDeptYgInfo(String type, String startTime, String endTime) {
        List<Statistics> frygs = statisticsDao.selectFrDeptYgInfo(type, startTime, endTime);
        int allDeptNum = 0;
        int index = -1;
        for (int i = 0; i < frygs.size(); i++) {
            if (frygs.get(i).getDate().equals("所有部门")) {
                allDeptNum = frygs.get(i).getNum();
                index = i;
            }
        }
        if (index != -1) {
            frygs.remove(index);
        }
        if (allDeptNum != 0) {
            for (int j = 0; j < frygs.size(); j++) {
                frygs.get(j).setNum(frygs.get(j).getNum() + allDeptNum);
            }
        }

        return frygs;
    }

    private List<Statistics> selectZrrDeptYgInfo(String type, String startTime, String endTime) {
        List<Statistics> zrrygs = statisticsDao.selectZrrDeptYgInfo(type, startTime, endTime);
        int allDeptNum = 0;
        int index = -1;
        for (int i = 0; i < zrrygs.size(); i++) {
            if (zrrygs.get(i).getDate().equals("所有部门")) {
                allDeptNum = zrrygs.get(i).getNum();
                index = i;
            }
        }
        if (index != -1) {
            zrrygs.remove(index);
        }

        if (allDeptNum != 0) {
            for (int j = 0; j < zrrygs.size(); j++) {
                zrrygs.get(j).setNum(zrrygs.get(j).getNum() + allDeptNum);
            }
        }

        return zrrygs;
    }
}
